<?php
/**
 * Excel管理类
 * comfiger
 * 2016-8-28
 **/
namespace Xijie\Model;
use Think\Model;

class ExcelModel extends Model{
	//定义表名称
	protected $tableName = 'users';
	
	/**
	 * 导出Excel
	 **/
	public function exportExcel($expTitle,$expCellName,$expTableData,$fileName, $titleLine=2, $contentLine=3, $type=0, $totalArray=array()){
        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
        $cellNum = count($expCellName);
        $dataNum = count($expTableData);
		
		import("Org.Util.PHPExcel");
		
        $objPHPExcel = new \PHPExcel();
        $objSheet = $objPHPExcel->getActiveSheet(0);
        $cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
		
		$objSheet->getColumnDimension('A')->setWidth(4); //5.43
		$objSheet->getColumnDimension('B')->setWidth(14);  //6.14
		$objSheet->getColumnDimension('C')->setWidth(18); //14
		$objSheet->getColumnDimension('D')->setWidth(6); //3.71
		$objSheet->getColumnDimension('E')->setWidth(6); //4.71
		$objSheet->getColumnDimension('F')->setWidth(9); //5.14
		$objSheet->getColumnDimension('G')->setWidth(9); //7
		$objSheet->getColumnDimension('H')->setWidth(9); //5.14
		$objSheet->getColumnDimension('I')->setWidth(9); //7.57
		$objSheet->getColumnDimension('J')->setWidth(9); //5.57
		$objSheet->getColumnDimension('K')->setWidth(9); //6.29
		$objSheet->getColumnDimension('L')->setWidth(9); //5.57
		$objSheet->getColumnDimension('M')->setWidth(9); //7.71
		$objSheet->getColumnDimension('N')->setWidth(10); //6.14
		$objSheet->getColumnDimension('O')->setWidth(10); //6.14
		$objSheet->getColumnDimension('P')->setWidth(10); //6
		$objSheet->getColumnDimension('Q')->setWidth(10); //5.29
		$objSheet->getColumnDimension('R')->setWidth(10); //7.43
		$objSheet->getColumnDimension('S')->setWidth(10); //6.86
		$objSheet->getColumnDimension('T')->setWidth(35); //6.86
		//变化字体大小适应宽度
		//$objSheet->getStyle('S')->getAlignment()->setShrinkToFit(true);
		$objSheet->getDefaultStyle()->getFont()->setName('Calibri');
		$objSheet->getDefaultStyle()->getFont()->setSize(9);
		//设置垂直居中
		$objSheet->getDefaultStyle()->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
		//设置左右居中
		$objSheet->getDefaultStyle()->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		//显示不完，自动换行
		$objSheet->getDefaultStyle()->getAlignment()->setWrapText(true);
		//备注靠左
		$objSheet->getStyle('T')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
		
		if($type){
        	$objSheet->getDefaultRowDimension()->setRowHeight(23); //设置默认行高
			//$objSheet->getDefaultRowDimension()->setRowHeight(-1); //设置自动高度
			$objSheet->setCellValue('A1', $expTitle); //设置A1值
	        $objSheet->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
	        $objSheet->getStyle('A1:T1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	       // $objSheet->getStyle('A1:S1')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)
			//			->getStartColor()->setRGB('7fde80');
			$objSheet->getStyle('A1:T1')->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
			$objSheet->getRowDimension('1')->setRowHeight(30);
			$objSheet->getStyle('A1')->getFont()->setBold(true);    //第一行是否加粗
        	$objSheet->getStyle('A1')->getFont()->setSize(16);

			//预先设置第2行合并标题
			$e2title = $type == 1 ? '收款明细' : '退款明细';
			$n2title = $type == 1 ? '收款方式' : '退款方式';
			$objSheet->setCellValue('E2', $e2title); //设置A1值
			$objSheet->setCellValue('N2', $n2title); //设置A1值
			//合并单元格
			$objSheet->mergeCells('E2:M2');//合并单元格
			$objSheet->mergeCells('N2:R2');//合并单元格
			$objSheet->getStyle('A2:T2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			//$objSheet->getStyle('A2:S2')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)
						//->getStartColor()->setRGB('7fde80');
			$objSheet->getStyle('A2:T2')->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
		}else{
			
		}
		
        for($i=0;$i<$cellNum;$i++){
			//HORIZONTAL_LEFT
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i]. $titleLine, $expCellName[$i][1]); //第三行开始
        }
        //上下合并单元格
        if($type){
        	$objSheet->setCellValue('A2', '序号'); //设置A1值
        	$objSheet->setCellValue('B2', '交款客户'); //设置A1值
        	$objSheet->setCellValue('C2', '合同号'); //设置A1值
        	$objSheet->setCellValue('D2', '房号'); //设置A1值
        	$objSheet->setCellValue('T2', '备注'); //设置A1值
        	//设置内容垂直剧中
        	$objSheet->getStyle('A2:T2')->getAlignment()->setVertical(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

        	$objSheet->mergeCells('A2:A3');//合并单元格
			$objSheet->mergeCells('B2:B3');//合并单元格
			$objSheet->mergeCells('C2:C3');//合并单元格
			$objSheet->mergeCells('D2:D3');//合并单元格VERTICAL_CENTER
			$objSheet->mergeCells('T2:T3');//合并单元格
			$objSheet->getStyle('A3:T3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			//$objSheet->getStyle('A3:S3')->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)
			//			->getStartColor()->setRGB('7fde80');
			$objSheet->getStyle('A3:T3')->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
        }
        // Miscellaneous glyphs, UTF-8   
        for($i=0;$i<$dataNum;$i++){
			for($j=0;$j<$cellNum;$j++){
				$objSheet->setCellValue($cellName[$j].($i+$contentLine), $expTableData[$i][$expCellName[$j][0]]);
				$objSheet->getStyle($cellName[$j].($i+$contentLine))->getAlignment()->setVertical(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
				//$objSheet->getStyle($cellName[$j].($i+$contentLine))->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('7fde80');
				$objSheet->getStyle($cellName[$j].($i+$contentLine))->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
			}             
        }
		//小计
		$xiaojiLine = $dataNum + 4;
		//合并A-D
		$objSheet->setCellValue('A'. $xiaojiLine, '合计'); 
		$objSheet->mergeCells('A'. $xiaojiLine .':D'. $xiaojiLine);//合并单元格
		$objSheet->getStyle('A'. $xiaojiLine .':D'. $xiaojiLine)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
		//$objSheet->getStyle('A'. $xiaojiLine .':D'. $xiaojiLine)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('7fde80');
		$objSheet->getStyle('A'. $xiaojiLine .':D'. $xiaojiLine)->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
        //开始统计
		for($j=4;$j<=19;$j++){
			if($j==19){
			}else{
				$objSheet->setCellValue($cellName[$j]. $xiaojiLine, '=SUM('. $cellName[$j] .'4:'. $cellName[$j] . ($dataNum+3) .')');
			}	
			$objSheet->getStyle($cellName[$j]. $xiaojiLine)->getAlignment()->setVertical(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			//$objSheet->getStyle($cellName[$j]. $xiaojiLine)->getFill()->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('7fde80');
			$objSheet->getStyle($cellName[$j]. $xiaojiLine)->getBorders()->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
		}
		/*
		//统计表格
        if($type){
        	$maxLine = $dataNum + 6;
        	$totalLineArray = array('现金','微信', '支付宝','POS机', '转账', '合计');

        	$htong = $type == 1 ? '合计收款' : '合计退款';
        	$objSheet->setCellValue('A'. $maxLine, $htong);
			$objSheet->getStyle('A'. $maxLine)->getAlignment()->setWrapText(true);
			
        	$objSheet->setCellValue('B'. $maxLine, '项目');
        	$objSheet->setCellValue('C'. $maxLine, '金额');
        	$objSheet->mergeCells('A'. $maxLine .':A'. ($maxLine+6));//合并单元格
        	$objSheet->getStyle('A'. $maxLine .':A'. ($maxLine+6))->getAlignment()
        			 ->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        	$objSheet->getStyle('A'. $maxLine .':A'. ($maxLine+6))->getAlignment()
        			->setVertical(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

			//$objSheet->getStyle('A'. $maxLine .':A'. ($maxLine+5))->getFill()
					 //->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('7fde80');
			$objSheet->getStyle('A'. $maxLine .':A'. ($maxLine+6))->getBorders()
					 ->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
			//设置单独样式
			$objSheet->getStyle('B'. ($maxLine))->getAlignment()
						 ->setVertical(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
				//$objSheet->getStyle('B'. ($maxLine))->getFill()
				//		 ->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('7fde80');
				$objSheet->getStyle('B'. ($maxLine))->getBorders()
						 ->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
			$objSheet->getStyle('C'. ($maxLine))->getAlignment()
						 ->setVertical(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
				//$objSheet->getStyle('C'. ($maxLine))->getFill()
				//		 ->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('7fde80');
				$objSheet->getStyle('C'. ($maxLine))->getBorders()
						 ->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);			 
        	for($tt=0;$tt<6;$tt++){
	        	$objSheet->setCellValue('B'. ($maxLine+1+$tt), $totalLineArray[$tt]);
				$objSheet->getStyle('B'. ($maxLine+1+$tt))->getAlignment()
						 ->setVertical(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
				//$objSheet->getStyle('B'. ($maxLine+1+$tt))->getFill()
				//		 ->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('7fde80');
				$objSheet->getStyle('B'. ($maxLine+1+$tt))->getBorders()
						 ->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);
				//赋值
				$objSheet->setCellValue('C'. ($maxLine+1+$tt), $totalArray[$tt]);
				$objSheet->getStyle('C'. ($maxLine+1+$tt))->getAlignment()
						 ->setVertical(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
				//$objSheet->getStyle('C'. ($maxLine+1+$tt))->getFill()
				//		 ->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setRGB('7fde80');
				$objSheet->getStyle('C'. ($maxLine+1+$tt))->getBorders()
						 ->getAllBorders()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THIN);		 
			}
        }
		*/
        //设置EXCEL中的tag名称
		$objPHPExcel->getActiveSheet()->setTitle($expTitle);
		$objPHPExcel->setActiveSheetIndex(0);
		//设置打印 页面 方向与大小（此为横向）
        $objPHPExcel->getActiveSheet()->getPageSetup()->setOrientation(\PHPExcel_Worksheet_PageSetup::ORIENTATION_LANDSCAPE);
        $objPHPExcel->getActiveSheet()->getPageSetup()->setPaperSize(\PHPExcel_Worksheet_PageSetup::PAPERSIZE_A4);
		//界面缩放到80%
		$objPHPExcel->getActiveSheet()->getPageSetup()->setScale(78);
		//设置页面边距 1英寸 = 2.54厘米
		$objPHPExcel->getActiveSheet()->getPageMargins()->setLeft(0.5);
		$objPHPExcel->getActiveSheet()->getPageMargins()->setRight(0.5);
		
		header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename='.$fileName.'.xls');  //日期为文件名后缀
        header('Cache-Control: max-age=0');
		
		import("Org.Util.PHPExcel.IOFactory.php");
		$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  
		$objWriter->save('php://output');
        exit;
    }
	
	/**
	 * 导出Excel
	 **/
	public function exportExcelBill($expTitle, $billNumber, $fileName, $imptimes, $company, $paymethod, $bigMoney, $money, $remark){
        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
		import("Org.Util.PHPExcel");
		
        $objPHPExcel = new \PHPExcel();
        $objSheet = $objPHPExcel->getActiveSheet(0);
        $cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
		//设置第一行
		$objSheet->getDefaultRowDimension()->setRowHeight(20); //设置默认行高
		//设置宽度
		$cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M');
		foreach($cellName as $k){
			if($k == "A"){
				$objSheet->getColumnDimension($k)->setWidth(4);
			}else{
				$objSheet->getColumnDimension($k)->setWidth(7);  
			}
		}
		//设置A1值
		$objSheet->setCellValue('A1', $expTitle); 
		$objSheet->mergeCells('A1:I1');//合并单元格
		$objSheet->getStyle('A1:I1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$objSheet->getStyle('A1:M1')->getBorders()->getBottom()->setBorderStyle(\PHPExcel_Style_Border::BORDER_DOUBLE);
		$objSheet->getRowDimension('1')->setRowHeight(30);
		$objSheet->getStyle('A1')->getFont()->setBold(FALSE);    //第一行是否加粗
		$objSheet->getStyle('A1')->getFont()->setSize(17);
		//单号
		$objSheet->setCellValue('J1', $billNumber); //设置A1值
		$objSheet->mergeCells('J1:M1');//合并单元格
		$objSheet->getStyle('J1')->getFont()->setSize(14);
		//预先设置第2行合并标题
		$objSheet->setCellValue('A2', '入账时间：'); //设置A1值
		$objSheet->mergeCells('A2:E2');//合并单元格
		$objSheet->getStyle('A2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
		//合并单元格
		$objSheet->setCellValue('F2', $imptimes); //设置A1值
		$objSheet->mergeCells('F2:M2');//合并单元格
		$objSheet->getStyle('F2:M2')->getBorders()->getBottom()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THICK);
		//设置第三行
		$objSheet->setCellValue('A3', '交款单位：'); //设置A1值
		$objSheet->mergeCells('A3:C3');//合并单元格
		$objSheet->getStyle('A3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
		$objSheet->setCellValue('D3', $company); //设置A1值
		$objSheet->mergeCells('D3:F3');//合并单元格
		$objSheet->getStyle('D3:F3')->getBorders()->getBottom()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THICK);
		$objSheet->setCellValue('G3', '收款方式：'); //设置A1值
		$objSheet->mergeCells('G3:I3');//合并单元格
		$objSheet->getStyle('G3')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
		$objSheet->setCellValue('J3', $paymethod); //设置A1值
		$objSheet->mergeCells('J3:M3');//合并单元格
		$objSheet->getStyle('J3:M3')->getBorders()->getBottom()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THICK);
		//设置第4行
		$objSheet->setCellValue('A4', '大写金额：'); //设置A1值
		$objSheet->mergeCells('A4:C4');//合并单元格
		$objSheet->getStyle('A4')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
		$objSheet->setCellValue('D4', $bigMoney); //设置A1值
		$objSheet->mergeCells('D4:F4');//合并单元格
		$objSheet->getStyle('D4:F4')->getBorders()->getBottom()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THICK);
		$objSheet->setCellValue('G4', '￥：'); //设置A1值
		$objSheet->mergeCells('G4:I4');//合并单元格
		$objSheet->getStyle('G4')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
		$objSheet->setCellValue('J4', $money); //设置A1值
		$objSheet->mergeCells('J4:M4');//合并单元格
		$objSheet->getStyle('J4:M4')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
		$objSheet->getStyle('J4:M4')->getBorders()->getBottom()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THICK);
		//设置第5行
		$objSheet->setCellValue('A5', '收款事由：'); //设置A1值
		$objSheet->mergeCells('A5:C5');//合并单元格
		$objSheet->getStyle('A5')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
		$objSheet->setCellValue('D5', $remark); //设置A1值
		$objSheet->mergeCells('D5:M5');//合并单元格
		$objSheet->getStyle('D5:M5')->getBorders()->getBottom()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THICK);
		//合并第6行
		$objSheet->mergeCells('A6:M6');//合并单元格
		$objSheet->getStyle('A6:M6')->getBorders()->getBottom()->setBorderStyle(\PHPExcel_Style_Border::BORDER_THICK);
		
		$objSheet->setCellValue('A7', '单'); 
		$objSheet->setCellValue('A8', '位'); 
		$objSheet->setCellValue('A9', '盖'); 
		$objSheet->setCellValue('A10', '章');
		$objSheet->getStyle('A7:A10')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
		$objSheet->setCellValue('H7', '财'); 
		$objSheet->setCellValue('H8', '务'); 
		$objSheet->setCellValue('H9', '主'); 
		$objSheet->setCellValue('H10', '管');
		$objSheet->getStyle('H7:H10')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
		$objSheet->setCellValue('I7', '记'); 
		$objSheet->setCellValue('I10', '账');
		$objSheet->getStyle('I7:I10')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
		$objSheet->setCellValue('J7', '出'); 
		$objSheet->setCellValue('J10', '纳');
		$objSheet->getStyle('J7:J10')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
		$objSheet->setCellValue('K7', '审'); 
		$objSheet->setCellValue('K10', '核');
		$objSheet->getStyle('K7:K10')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
		$objSheet->setCellValue('L7', '经'); 
		$objSheet->setCellValue('L10', '办');
		$objSheet->getStyle('L7:L10')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
		
        //设置EXCEL中的tag名称
		$objPHPExcel->getActiveSheet()->setTitle($expTitle);
		$objPHPExcel->setActiveSheetIndex(0);
		
		header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename='.$fileName.'.xls');  //日期为文件名后缀
        header('Cache-Control: max-age=0');
		
		import("Org.Util.PHPExcel.IOFactory.php");
		$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  
		$objWriter->save('php://output');
		//保存收据单号
		M('cost')->data(array('createtime' => time()))->add();
        exit;
    }
	
	/**
	 * 读取Excel
	 *
	 **/
	public function readExcel($file, $extension, $i=6){
		import("Org.Util.PHPExcel");
		import("Org.Util.PHPExcel.IOFactory.php");
		if( $extension =='xlsx'){
			$type = 'Excel2007';
		}else{
			$type = 'Excel5';
		}
		$reader = \PHPExcel_IOFactory::createReader($type); //设置以Excel5格式(Excel97-2003工作簿)
		
		$PHPExcel = $reader->load($file); // 载入excel文件
		$sheet = $PHPExcel->getSheet(0); // 读取第一個工作表
		$highestRow = $sheet->getHighestRow(); // 取得总行数
		$highestColumm = $sheet->getHighestColumn(); // 取得总列数
		
		/** 循环读取每个单元格的数据 */
		for ($row = $i; $row <= $highestRow; $row++){//行数是以第1行开始
			for ($column = 'A'; $column <= $highestColumm; $column++) {//列数是以A列开始
				//$dataset[] = $sheet->getCell($column.$row)->getValue(); getFont()
				$cell = $sheet->getCell($column.$row)->getValue();
				if(is_object($cell))  $cell= $cell->__toString(); //转化文本
				$dataset[$column] = $cell;
			}
			$array[$row] = $dataset;
		}
		
		return $array;
	}
	
	/**
	 * 导出数据
	 *
	 **/
	public function exportExcelHydropower($expTitle, $expCellName, $expTableData, $fileName){
		$xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
        $cellNum = count($expCellName); //字段数
        $dataNum = count($expTableData); //数据行数

		import("Org.Util.PHPExcel");
        $objPHPExcel = new \PHPExcel();
        $objSheet = $objPHPExcel->getActiveSheet(0);
        $cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
       	
       	$objSheet->getDefaultRowDimension()->setRowHeight(20); //设置默认行高
       	$objSheet->getColumnDimension()->setWidth(12);
        $objSheet->setCellValue('A1', "房间号"); //设置A1值
		$objSheet->mergeCells('A1:A2');//合并单元格
		$objSheet->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

		$objSheet->setCellValue('B1', "用电"); //设置B1值
		$objSheet->mergeCells('B1:N1');//合并单元格
		$objSheet->getStyle('B1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		
		$objSheet->setCellValue('O1', "用水"); //设置B1值
		$objSheet->mergeCells('O1:AA1');//合并单元格
		$objSheet->getStyle('O1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

		for($i=1;$i<$cellNum;$i++){
			$objSheet->getColumnDimension($cellName[$i])->setWidth(8);
			$objSheet->setCellValue($cellName[$i].'2', $expCellName[$i][1]); //设置A1值
			//居中展示
			$objSheet->getStyle($cellName[$i].'2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		}

        //设置EXCEL中的tag名称
		$objPHPExcel->getActiveSheet()->setTitle($expTitle);
		$objPHPExcel->setActiveSheetIndex(0);
		//开始加载数据
		
		for($i=0;$i<$dataNum;$i++){
			for($j=0;$j<$cellNum;$j++){
				$objSheet->setCellValue($cellName[$j].($i+3), $expTableData[$i][$expCellName[$j][0]]);

				$objSheet->getStyle($cellName[$j].($i+3))->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			}             
        }

		header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename='.$fileName.'.xls');  //日期为文件名后缀
        header('Cache-Control: max-age=0');
		
		import("Org.Util.PHPExcel.IOFactory.php");
		$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  
		$objWriter->save('php://output');
        exit;
	}

	/**
	 * 导出账单
	 **/
	public function exportExcelCostOrder($expTitle, $expCellName, $expTableData, $fileName){
		$xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
        $cellNum = count($expCellName); //字段数
        $dataNum = count($expTableData); //数据行数

		import("Org.Util.PHPExcel");
        $objPHPExcel = new \PHPExcel();
        $objSheet = $objPHPExcel->getActiveSheet(0);
        $cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
       	
       	$objSheet->getDefaultRowDimension()->setRowHeight(20); //设置默认行高
       	$objSheet->getColumnDimension()->setWidth(20);
        for($i=0;$i<$cellNum;$i++){
        	$objSheet->setCellValue($cellName[$i].'1', $expCellName[$i][1]); //设置A1值
        	$objSheet->getStyle($cellName[$i].'1')->getAlignment()
        			->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        	if(!$i){
        		$objSheet->getColumnDimension($cellName[$i])->setWidth(8);
        	}		
        }
        
        //设置EXCEL中的tag名称
		$objPHPExcel->getActiveSheet()->setTitle($expTitle);
		$objPHPExcel->setActiveSheetIndex(0);
		//开始加载数据
		
		for($i=0;$i<$dataNum;$i++){
			for($j=0;$j<$cellNum;$j++){
				$objSheet->setCellValue($cellName[$j].($i+2), $expTableData[$i][$expCellName[$j][0]]);
				$objSheet->getStyle($cellName[$j].($i+2))->getAlignment()
						->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			}             
        }
		
		header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename='.$fileName.'.xls');  //日期为文件名后缀
        header('Cache-Control: max-age=0');
		
		import("Org.Util.PHPExcel.IOFactory.php");
		$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  
		$objWriter->save('php://output');
        exit;
	}
	
	public function exportExcelHydropowerTemp($expTitle, $expCellName, $expTableData, $fileName){
		$xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
        $cellNum = count($expCellName); //字段数
        $dataNum = count($expTableData); //数据行数

		import("Org.Util.PHPExcel");
        $objPHPExcel = new \PHPExcel();
        $objSheet = $objPHPExcel->getActiveSheet(0);
        $cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
       	
       //	$objSheet->getDefaultRowDimension()->setRowHeight(20); //设置默认行高
       //	$objSheet->getColumnDimension()->setWidth(12);
        $objSheet->setCellValue('A1', "编号"); //设置A1值
		$objSheet->mergeCells('A1:A2');//合并单元格
		$objSheet->getStyle('A1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

		$objSheet->setCellValue('B1', "房间号"); //设置B1值
		$objSheet->mergeCells('B1:B2');//合并单元格
		$objSheet->getStyle('B1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		
		$objSheet->setCellValue('C1', "抄表月份"); //设置B1值
		$objSheet->mergeCells('C1:C2');//合并单元格
		$objSheet->getStyle('C1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		
		$objSheet->setCellValue('D1', "水"); //设置B1值
		$objSheet->mergeCells('D1:I1');//合并单元格
		$objSheet->getStyle('D1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		
		$objSheet->setCellValue('J1', "电"); //设置B1值
		$objSheet->mergeCells('J1:O1');//合并单元格
		$objSheet->getStyle('J1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		
		$objSheet->setCellValue('P1', "气"); //设置B1值
		$objSheet->mergeCells('P1:U1');//合并单元格
		$objSheet->getStyle('P1')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		
		for($i=3;$i<=$cellNum;$i++){
			$objSheet->getColumnDimension($cellName[$i])->setWidth(8);
			$objSheet->setCellValue($cellName[$i].'2', $expCellName[$i][1]); //设置A1值
			//居中展示
			$objSheet->getStyle($cellName[$i].'2')->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		}
		
        //设置EXCEL中的tag名称
		$objPHPExcel->getActiveSheet()->setTitle($expTitle);
		$objPHPExcel->setActiveSheetIndex(0);
		//开始加载数据
		for($i=0;$i<$dataNum;$i++){
			for($j=0;$j<$cellNum;$j++){
				$objSheet->setCellValue($cellName[$j].($i+3), $expTableData[$i][$expCellName[$j][0]]);
				$objSheet->getStyle($cellName[$j].($i+3))->getAlignment()
						->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			}             
        }
		
		header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename='.$fileName.'.xls');  //日期为文件名后缀
        header('Cache-Control: max-age=0');
		
		import("Org.Util.PHPExcel.IOFactory.php");
		$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  
		$objWriter->save('php://output');
        exit;
	}
	
}
